﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using OpenSoftStore.Orm;
using OpenSoftStore.Orm.Model;
using System.Linq;
using System.Data;
using OpenSoftStore.Orm.View;
using OpenSoftStore.Orm.Logic;

namespace OpenSoftStore.BusinessLogic.Product
{
    public class ProductLogic : ABusinessLogic
    {
        public ProductLogic()
        {
        }

        #region Methods


        #region Product

        public DataTable GetProductList(string strWhere = "")
        {
            string sql = "Select  P.*, E.*,prov.Name as ProvName,T.Name as TreeName,(E.Store1+E.Store2+E.Store3+E.Store4) as AllStore";
            sql += " From ((T_Product as P inner join T_ProductExt as E on P.Id=E.ProductId) ";
            sql += " left join T_Provider as prov on prov.Id=E.ProviderId) ";
            sql += " left join T_Tree as T on T.Id=P.TreeId ";
            DataSet dataSet = Spa.RunSqLcmdDataSet(sql + strWhere);
            if (dataSet == null) return new DataTable();
            if (dataSet.Tables.Count > 0)
                return dataSet.Tables[0];
            else
                return null;
        }

        public IEnumerable<V_Product> GetProduts(string strWhere = "")
        {
            string sql = "Select  P.*, E.*,prov.Name as ProvName,T.Name as TreeName";
            sql += " From ((T_Product as P inner join T_ProductExt as E on P.Id=E.ProductId) ";
            sql += " inner join T_Provider as prov on prov.Id=E.ProviderId) ";
            sql += " inner join T_Tree as T on T.Id=P.TreeId ";
            return Spa.SelectWhere<V_Product>(sql + strWhere);
        }

        public IEnumerable<V_Product> GetProdutView(string strWhere = "", string orderStr = "")
        {
            string sql = "Select  P.*, E.*,prov.Name as ProvName,T.Name as TreeName,(E.Store1+E.Store2+E.Store3+E.Store4) as AllStore";
            sql += " From ((T_Product as P inner join T_ProductExt as E on P.Id=E.ProductId) ";
            sql += " left join T_Provider as prov on prov.Id=E.ProviderId) ";
            sql += " left join T_Tree as T on T.Id=P.TreeId ";
            return Spa.SelectWhere<V_Product>(sql + strWhere);
        }

        public DataTable GetProductDataTable(string strWhere = "", string orderStr = "")
        {
            string sql = "Select  P.*, E.*,prov.Name as ProvName,T.Name as TreeName,(E.Store1+E.Store2+E.Store3+E.Store4) as AllStore";
            sql += " From ((T_Product as P inner join T_ProductExt as E on P.Id=E.ProductId) ";
            sql += " inner join T_Provider as prov on prov.Id=E.ProviderId) ";
            sql += " inner join T_Tree as T on T.Id=P.TreeId ";
            sql += " where P.IsProject ='否' ";
            var data = Spa.RunSqLcmdDataSet(sql + strWhere);
            if (data.Tables == null || data.Tables.Count == 0) return new DataTable();
            return data.Tables[0];
        }

        public IEnumerable<V_Product> GetProdutAndServiceView(string strWhere = "")
        {
            string sql = "select P.IsProject,P.Id, P.Name,P.ProductNo,E.BrokerageType,E.BrokerageNum,E.SalePrice,T.Name as TreeName  from";
            sql += "  (T_Product as P inner join T_ProductExt as E on P.Id=E.ProductId)";
            sql += "  inner join T_Tree as T on T.Id = P.TreeId ";

            return Spa.SelectWhere<V_Product>(sql + strWhere);
        }

        public int AddProduct(T_Product product, T_ProductExt productExt)
        {
            Spa.SetCountNo("T_Product", product.ProductNo);
            Spa.AddObject(product);
            Spa.AddObject(productExt);
            return Spa.SaveChanges();
        }

        public int UpdateProduct(T_Product product, T_ProductExt productExt)
        {
            Spa.UpdateObject(product);
            Spa.UpdateObject(productExt);
            return Spa.SaveChanges();
        }

        public int DeleteProduct(T_Product product)
        {
            var productExts = Spa.Where<T_ProductExt>(p => p.ProductId == product.Id);
            Spa.DeleteObjects(productExts);
            Spa.DeleteObject(product);
            return Spa.SaveChanges();
        }

        #endregion

        #region Provider
        public IEnumerable<T_Provider> GetProvider()
        {
            return Spa.SelectWhere<T_Provider>("select Id,Name from T_Provider");
        }
        #endregion


        #region StoreIO
        public int BillPayStoreIO(T_Bill bill, List<T_BillItem> billItems)
        {
            List<T_StoreIOItem> list = new List<T_StoreIOItem>();

            T_StoreIO store = new T_StoreIO
            {
                Id = Guid.NewGuid().ToString("D"),
                StroeIONo = bill.BillNo,
                AdminId = bill.AdminId,
                BuzType = "销售",
                Type = "出库",
                BuzUserId = "46872df4-47b1-4875-bdbb-1f8f8d1023ee",
                ShopId = bill.ShopId,
                AddTime = bill.PayTime,
            };
            int count = 0;
            foreach (var item in billItems)
            {
                if (item.IsProject == "是")
                {
                    string sql = " select svc.ProductId, svc.Amount,P.SubAmount,P.SalePrice from T_ProductExt as P inner join T_ServiceWithProduct as svc on svc.ProductId=P.ProductId";
                    string whereStr = " where svc.ServiceId='" + item.ProductId + "'";
                    var products = Spa.SelectWhere<V_Product>(sql + whereStr).ToList();
                    foreach (var product in products)
                    {
                        count++;
                        decimal price = product.SalePrice * item.Amount;
                        store.AllPrice += price;
                        list.Add(new T_StoreIOItem
                        {
                            Id = Guid.NewGuid().ToString("D"),
                            Amount = product.Amount / product.SubAmount * item.Amount,
                            Pos = item.Pos,
                            Price = price,
                            ProductId = product.ProductId,
                            StoreIOId = store.Id,
                        });
                    }
                }
                else
                {
                    count++;
                    decimal price = item.Price * item.Amount;
                    list.Add(new T_StoreIOItem
                    {
                        Id = Guid.NewGuid().ToString("D"),
                        Amount = item.Amount,
                        Pos = item.Pos,
                        Price = price,
                        ProductId = item.ProductId,
                        StoreIOId = store.Id,
                    });
                    store.AllPrice += price;
                }

            }
            Spa.SetCountNo("T_StoreIO", store.StroeIONo);
            store.NeedPay = store.AllPrice;
            store.RealPay = store.AllPrice;
            store = count > 0 ? store : new T_StoreIO();
            Spa.AddObject(store);
            Spa.AddObjects(list);
            return Spa.SaveChanges();
        }

        /// <summary>
        /// 出入库单据添加
        /// </summary>
        /// <param name="storeIO"></param>
        /// <param name="storeItems"></param>
        /// <param name="isInput"></param>
        /// <returns></returns>
        public int AddStoreIO(T_StoreIO storeIO, List<V_StoreIOItem> storeItems, bool isInput = true)
        {
            List<T_StoreIOItem> list = ConvertStoreItems(storeIO, storeItems, isInput);
            Spa.SetCountNo("T_StoreIO", storeIO.StroeIONo);
            Spa.AddObject(storeIO);
            Spa.AddObjects(list);
            return Spa.SaveChanges();
        }

        /// <summary>
        /// 出入库单据修改
        /// </summary>
        /// <param name="storeIO"></param>
        /// <param name="storeItems"></param>
        /// <param name="isInput"></param>
        /// <returns></returns>
        public int UpdateStoreIO(T_StoreIO storeIO, List<V_StoreIOItem> storeItems, bool isInput = true)
        {
            var oldStoreItems = Spa.Where<T_StoreIOItem>(s => s.StoreIOId == storeIO.Id).ToList();
            if (storeIO.Type == "入库")
            {
                foreach (var item in oldStoreItems)
                {
                    V_StoreIOItem view = new V_StoreIOItem();
                    view.Pos2 = item.Pos;
                    view.ProductId = item.ProductId;
                    view.Amount = item.Amount;
                    StoreOut(view);
                }
            }
            else
            {
                foreach (var item in oldStoreItems)
                {
                    V_StoreIOItem view = new V_StoreIOItem();
                    view.Pos = item.Pos;
                    view.ProductId = item.ProductId;
                    view.Amount = item.Amount;
                    StoreInput(view);
                }
            }
            Spa.DeleteObjects(oldStoreItems);

            List<T_StoreIOItem> list = ConvertStoreItems(storeIO, storeItems, isInput);
            Spa.UpdateObject(storeIO);
            Spa.AddObjects(list);
            return Spa.SaveChanges();
        }

        private List<T_StoreIOItem> ConvertStoreItems(T_StoreIO storeIO, List<V_StoreIOItem> storeItems, bool isInput)
        {
            List<T_StoreIOItem> list = new List<T_StoreIOItem>();
            foreach (var item in storeItems)
            {
                list.Add(new T_StoreIOItem
                {
                    StoreIOId = storeIO.Id,
                    Amount = item.Amount + (item.SubNum / item.SubAmount),
                    ProductId = item.ProductId,
                    Price = item.Price,
                    Pos = isInput ? item.Pos : item.Pos2,
                });
                if (isInput)
                {
                    StoreInput(item);
                }
                else
                {
                    StoreOut(item);
                }
            }

            decimal allPrice = storeItems.Sum(s => s.Price * s.Amount + (s.Price / s.SubAmount * s.SubNum));
            storeIO.AllPrice = allPrice;
            storeIO.NeedPay = allPrice;
            return list;
        }

        /// <summary>
        /// 库存调拨单据保存方法
        /// </summary>
        /// <param name="storeIn"></param>
        /// <param name="storeOut"></param>
        /// <param name="storeItems"></param>
        /// <returns></returns>
        public int AddStoreIO(T_StoreIO storeIn, T_StoreIO storeOut, List<V_StoreIOItem> storeItems)
        {
            List<T_StoreIOItem> storeInItems = new List<T_StoreIOItem>();
            List<T_StoreIOItem> storeOutItems = new List<T_StoreIOItem>();

            storeOutItems = ConvertStoreItems(storeOut, storeItems, false);
            storeInItems = ConvertStoreItems(storeIn, storeItems, true);

            var list = storeInItems.Concat(storeOutItems);
            Spa.SetCountNo("T_StoreIO", storeIn.StroeIONo);
            Spa.AddObjects(new List<T_StoreIO> { storeOut, storeIn });
            Spa.AddObjects(list);
            return Spa.SaveChanges();
        }

        /// <summary>
        /// 更新库存调拨
        /// </summary>
        /// <param name="storeIn"></param>
        /// <param name="storeOut"></param>
        /// <param name="storeItems"></param>
        /// <returns></returns>
        public int UpdateStoreIO(T_StoreIO storeIn, T_StoreIO storeOut, List<V_StoreIOItem> storeItems)
        {
            var oldStoreOutItems = Spa.Where<T_StoreIOItem>(s => s.StoreIOId == storeOut.Id).ToList();
            var oldStoreInItems = Spa.Where<T_StoreIOItem>(s => s.StoreIOId == storeIn.Id).ToList();

            foreach (var item in oldStoreOutItems)
            {
                V_StoreIOItem view = new V_StoreIOItem();
                view.Pos = item.Pos;
                view.ProductId = item.ProductId;
                view.Amount = item.Amount;
                StoreInput(view);
            }

            foreach (var item in oldStoreInItems)
            {
                V_StoreIOItem view = new V_StoreIOItem();
                view.Pos2 = item.Pos;
                view.ProductId = item.ProductId;
                view.Amount = item.Amount;
                StoreOut(view);
            }

            Spa.DeleteObjects(oldStoreOutItems);
            Spa.DeleteObjects(oldStoreInItems);

            List<T_StoreIOItem> storeInItems = new List<T_StoreIOItem>();
            List<T_StoreIOItem> storeOutItems = new List<T_StoreIOItem>();

            storeOutItems = ConvertStoreItems(storeOut, storeItems, false);
            storeInItems = ConvertStoreItems(storeIn, storeItems, true);

            var list = storeInItems.Concat(storeOutItems);
            Spa.UpdateObjects(new List<T_StoreIO> { storeOut, storeIn });
            Spa.AddObjects(list);
            return Spa.SaveChanges();
        }

        public DataTable GetStoreList(string strWhere = "")
        {
            string sql = "Select P.Name,S.* from (T_StoreIO as S inner join T_Provider as P on S.BuzUserId=P.Id) ";
            DataSet dataSet = Spa.RunSqLcmdDataSet(sql + strWhere);
            if (dataSet.Tables.Count == 0) {
                return new DataTable();
            }
            return dataSet.Tables[0];
        }

        public DataTable GetStoreItemList(string strWhere = "")
        {
            string sql = "Select P.Name, P.Unit, S.*, S.Price * S.Amount as AllPrice  from T_StoreIOItem as S inner join T_Product as P on S.ProductId=P.Id";
            DataSet dataSet = Spa.RunSqLcmdDataSet(sql + strWhere);
            if (dataSet.Tables.Count == 0)
            {
                return new DataTable();
            }
            return dataSet.Tables[0];
        }

        public IEnumerable<V_StoreIOItem> GetStoreItemView(string strWhere = "")
        {
            string sql = "Select P.Name, P.Unit, S.*, S.Pos as Pos2, S.Price * S.Amount as AllPrice  from T_StoreIOItem as S inner join T_Product as P on S.ProductId=P.Id";
            return Spa.SelectWhere<V_StoreIOItem>(sql + strWhere);
        }

        public int DeleteStoreIO(string id)
        {
            var storeItems = Spa.Where<T_StoreIOItem>(s => s.StoreIOId == id).ToList();
            Spa.DeleteObjects(storeItems);
            Spa.DeleteObject(new T_StoreIO { Id = id });
            return Spa.SaveChanges();
        }

        public void StoreInput(V_StoreIOItem storeItem)
        {
            var productExt = Spa.Where<T_ProductExt>(p => p.ProductId == storeItem.ProductId).FirstOrDefault();
            var storeNum = storeItem.Amount + (storeItem.SubNum / storeItem.SubAmount);
            switch (storeItem.Pos)
            {
                case "Store1":
                    productExt.Store1 += storeNum;
                    break;
                case "Store2":
                    productExt.Store2 += storeNum;
                    break;
                case "Store3":
                    productExt.Store3 += storeNum;
                    break;
                case "Store4":
                    productExt.Store4 += storeNum;
                    break;
                default:
                    break;
            }
            Spa.Update<T_ProductExt>(productExt);
        }

        public void StoreOut(V_StoreIOItem storeItem)
        {
            var productExt = Spa.Where<T_ProductExt>(p => p.ProductId == storeItem.ProductId).FirstOrDefault();
            var storeNum = storeItem.Amount + (storeItem.SubNum / storeItem.SubAmount);
            switch (storeItem.Pos2)
            {
                case "Store1":
                    productExt.Store1 -= storeNum;
                    break;
                case "Store2":
                    productExt.Store2 -= storeNum;
                    break;
                case "Store3":
                    productExt.Store3 -= storeNum;
                    break;
                case "Store4":
                    productExt.Store4 -= storeNum;
                    break;
                default:
                    break;
            }
            Spa.Update<T_ProductExt>(productExt);
        }

        /// <summary>
        /// 开单计算库存数量
        /// </summary>
        /// <param name="calcStores"></param>
        public void CalculateStoreAmount(List<L_CalculateStore> calcStores)
        {

            foreach (var item in calcStores)
            {
                if (item.IsProject == "是")
                {
                    var products = Spa.Where<T_ServiceWithProduct>(s => s.ServiceId == item.ProductId).ToList();
                    foreach (var product in products)
                    {
                        var productExt = Spa.Where<T_ProductExt>(p => p.ProductId == product.ProductId).FirstOrDefault();

                        switch (item.Pos)
                        {
                            case "Store1":
                                productExt.Store1 = productExt.Store1 - (product.Amount / productExt.SubAmount * item.Amount);
                                break;
                            case "Store2":
                                productExt.Store2 = productExt.Store2 - (product.Amount / productExt.SubAmount * item.Amount);
                                break;
                            case "Store3":
                                productExt.Store3 = productExt.Store3 - (product.Amount / productExt.SubAmount * item.Amount);
                                break;
                            case "Store4":
                                productExt.Store4 = productExt.Store4 - (product.Amount / productExt.SubAmount * item.Amount);
                                break;
                            default:
                                break;
                        }
                        Spa.Update<T_ProductExt>(productExt);
                    }
                }
                else
                {
                    var productExt = Spa.Where<T_ProductExt>(p => p.ProductId == item.ProductId).FirstOrDefault();
                    switch (item.Pos)
                    {
                        case "Store1":
                            productExt.Store1 -= item.Amount;
                            break;
                        case "Store2":
                            productExt.Store2 -= item.Amount;
                            break;
                        case "Store3":
                            productExt.Store3 -= item.Amount;
                            break;
                        case "Store4":
                            productExt.Store4 -= item.Amount;
                            break;
                        default:
                            break;
                    }
                    Spa.Update<T_ProductExt>(productExt);
                }
            }
        }

        /// <summary>
        /// 开单计算提成
        /// </summary>
        /// <param name="billItems"></param>
        /// <returns></returns>
        public int CalculateBorakerage(T_Bill bill, List<T_BillItem> billItems)
        {
            List<T_Borakerage> borakerages = new List<T_Borakerage>();
            foreach (T_BillItem item in billItems)
            {
                if (!string.IsNullOrEmpty(item.DinnerId))
                    item.Price = item.DinnerSaleItemPrice;
                T_Borakerage borakerage = new T_Borakerage
                    {
                        Id = Guid.NewGuid().ToString("D"),
                        AdminId = item.AdminId,
                        BillId = item.BillId,
                        BillItemId = item.Id,
                        ProductId = item.ProductId
                    };
                decimal borakerageNum = 0;
                if (item.BorakerageType == "按金额提成")
                {
                    borakerage.BrokerageType = "按金额提成";
                    borakerageNum = item.BorakerageNum * item.Amount;
                    borakerage.BroNum = borakerageNum;
                }
                else if (item.BorakerageType == "按利润比提成")
                {
                    borakerage.BrokerageType = "按利润比提成";
                    var admin = Spa.Where<T_Admin>(a => a.Id == item.AdminId).FirstOrDefault();
                    if (item.IsProject == "是")
                    {
                        var products = Spa.Where<T_ServiceWithProduct>(s => s.ServiceId == item.ProductId).ToList();
                        decimal income = item.Price * item.Discount * item.Amount;//服务的收入
                        decimal cost = (products.Sum(s => s.Cost)) * item.Amount;
                        decimal profit = income - cost;//服务的利润
                        if (admin.RoyaltyRate > 0)
                        {
                            borakerageNum = profit * admin.RoyaltyRate;
                            borakerage.RoyaltyRate = admin.RoyaltyRate;
                        }
                        else
                        {
                            borakerageNum = profit * item.BorakerageNum;
                            borakerage.RoyaltyRate = item.BorakerageNum;
                        }
                        borakerage.Cost = cost;
                        borakerage.BroNum = borakerageNum;
                    }
                    else
                    {
                        var productExt = Spa.Where<T_ProductExt>(p => p.ProductId == item.ProductId).FirstOrDefault();
                        decimal cost = productExt.InPrice * item.Amount;
                        decimal profit = (item.Price * item.Discount * item.Amount) - cost;
                        if (admin.RoyaltyRate > 0)
                        {
                            borakerageNum = profit * admin.RoyaltyRate;
                            borakerage.RoyaltyRate = admin.RoyaltyRate;
                        }
                        else
                        {
                            borakerageNum = profit * item.BorakerageNum;
                            borakerage.RoyaltyRate = item.BorakerageNum;
                        }
                        borakerage.Cost = cost;
                        borakerage.BroNum = borakerageNum;
                    }
                }
                borakerage.Amount = item.Amount;
                borakerage.SalePrice = item.Price;
                borakerage.Type = "开单";
                borakerage.AddTime = DateTime.Now;
                borakerage.ObjectNo = bill.BillNo;
                borakerages.Add(borakerage);
            }
            Spa.AddObjects(borakerages);
            return Spa.SaveChanges();
        }

        /// <summary>
        /// 充值和办套餐算提成
        /// </summary>
        /// <param name="borakerages"></param>
        /// <returns></returns>
        public int CalculateBorakerage(List<T_Borakerage> borakerages)
        {
            Spa.AddObjects(borakerages);
            return Spa.SaveChanges();
        }


        public DataTable GetStoreIoDataTable(DateTime dtFrom, DateTime dtTo, string strWhere = "")
        {
            string sql = string.Format(" SELECT distinct t5.Id, t5.productNo as ProductNo, t5.Name AS ProductName, t5.Unit, t5.Specification ,  t5.productModel as ProductModel , iif(isnull([t1.amountInSumarizeBeforeDate]),0,[t1.amountInSumarizeBeforeDate])-iif(isnull([t2.amountOutSumarizeBeforeDate]),0,[t2.amountOutSumarizeBeforeDate]) AS BeginAmount,  iif(isnull([t1.priceInSumarizeBeforeDate]),0,[t1.priceInSumarizeBeforeDate])-iif(isnull([t2.priceOutSumarizeBeforeDate]),0,[t2.priceOutSumarizeBeforeDate]) AS BeginPrice,  iif(isnull([t3.amountInSumarizeDuringDate]),0,[t3.amountInSumarizeDuringDate]) AS InAmount,  iif(isnull([t3.priceInSumarizeDuringDate]),0,[t3.priceInSumarizeDuringDate]) AS InPrice,  iif(isnull([t4.amountOutSumarizeDuringDate]),0,[t4.amountOutSumarizeDuringDate]) AS OutAmount,  iif(isnull([t4.priceOutSumarizeDuringDate]),0,[t4.priceOutSumarizeDuringDate]) AS OutPrice,  iif(isnull([t1.amountInSumarizeBeforeDate]),0,[t1.amountInSumarizeBeforeDate])-iif(isnull([t2.amountOutSumarizeBeforeDate]),0,[t2.amountOutSumarizeBeforeDate])+ iif(isnull([t3.amountInSumarizeDuringDate]),0,[t3.amountInSumarizeDuringDate])-iif(isnull([t4.amountOutSumarizeDuringDate]),0,[t4.amountOutSumarizeDuringDate]) AS EndAmount, iif(isnull([t1.priceInSumarizeBeforeDate]),0,[t1.priceInSumarizeBeforeDate])-iif(isnull([t2.priceOutSumarizeBeforeDate]),0,[t2.priceOutSumarizeBeforeDate])+ iif(isnull([t3.priceInSumarizeDuringDate]),0,[t3.priceInSumarizeDuringDate])-iif(isnull([t4.priceOutSumarizeDuringDate]),0,[t4.priceOutSumarizeDuringDate]) AS EndPrice  FROM (( (( (              (select distinct  c.ShopId as shopId,a.Id as Id , a.Name as Name ,a.Specification as Specification , a.Unit as Unit, a.ProductNo as productNo,a.Model as productModel,a.ShortName as shortName       from T_Product a,T_StoreIO c,T_StoreIOItem d,T_Shop b where a.Id=d.ProductId and c.Id=d.StoreIOId  and a.IsProject='否' )  AS t5  LEFT JOIN [select d.ProductId as productId,sum(d.Amount) as amountInSumarizeBeforeDate,sum(d.Amount*d.Price) as priceInSumarizeBeforeDate from  T_StoreIO c,T_StoreIOItem d  where  (( c.Type='入库' and c.BuzType not like '仓库调入' ) or (c.Type='盘点' and c.BuzType ='盘盈'))    and c.AddTime<#{0}# and c.Id=d.StoreIOId  group by d.ProductId ]. AS t1 ON t5.Id=t1.productId)  LEFT JOIN [select d.ProductId as productId,sum(d.Amount) as amountOutSumarizeBeforeDate,sum(d.Amount*d.Price) as priceOutSumarizeBeforeDate from   T_StoreIO c,T_StoreIOItem d  where (( c.Type='出库' and c.BuzType not like '仓库调出' ) or (c.Type='盘点' and c.BuzType ='盘亏'))  and c.AddTime<#{0}# and c.Id=d.StoreIOId  group by d.ProductId ]. AS t2 ON t5.Id=t2.productId)  LEFT JOIN [select d.ProductId as productId,sum(d.Amount) as amountInSumarizeDuringDate,sum(d.Amount*d.Price) as priceInSumarizeDuringDate from  T_StoreIO c,T_StoreIOItem d where (( c.Type='入库' and c.BuzType not like '仓库调入') or (c.Type='盘点' and c.BuzType ='盘盈'))  and c.AddTime>=#{0}# and c.AddTime<=#{1}# and c.Id=d.StoreIOId  group by d.ProductId ]. AS t3 ON t5.Id=t3.productId)  LEFT JOIN [select d.ProductId as productId,sum(d.Amount) as amountOutSumarizeDuringDate,sum(d.Amount*d.Price) as priceOutSumarizeDuringDate from  T_StoreIO c,T_StoreIOItem d where ((c.Type='出库' and  c.BuzType not like '仓库调出') or (c.Type='盘点' and c.BuzType ='盘亏')) and c.AddTime>=#{0}# and c.AddTime<=#{1}# and c.Id=d.StoreIOId  group by d.ProductId ]. AS t4 ON t5.Id=t4.productId  ) Left join  (select distinct c.ShopId as shopId,d.ProductId as productId             from  T_StoreIO c,T_StoreIOItem d where c.Id=d.StoreIOId) as t6  on t5.Id=t6.productId) ", dtFrom, dtTo);
            if (!string.IsNullOrEmpty(strWhere)) sql += strWhere;
            var data = Spa.RunSqLcmdDataSet(sql);
            if (data == null) return new DataTable();
            if (data.Tables.Count == 0) return new DataTable();
            return data.Tables[0];
        }
        public DataTable GetStoreIoItemDataTable(DateTime dtFrom, DateTime dtTo, string productId, string strWhere = "", string strOrderBy = "")
        {
            string sql = string.Format(" SELECT  e.ProductNo As ProductNo, e.Name AS ProductName,  e.Unit AS ProductUnit ,  e.Specification AS ProductSpecication,  e.Model as ProductModel, c.Type AS Type,  f.Name AS ProductShopName, c.BuzType, d.Price*d.Amount AS AmountSumarize,  d.Price, d.Amount,  d.Pos, g.Name as ProviderName, c.AddTime FROM T_StoreIO AS c, T_StoreIOItem AS d, T_Product AS e, T_Shop AS f, T_Provider AS g WHERE   c.AddTime >= #{0}# And  c.AddTime<= #{1}# AND c.Id=d.StoreIOId AND d.ProductId=e.Id AND c.ShopId=f.Id AND c.BuzUserId=g.Id AND e.Id = '{2}'   ", dtFrom, dtTo, productId);
            if (!string.IsNullOrEmpty(strWhere)) sql += strWhere;
            if (string.IsNullOrEmpty(strOrderBy)) strOrderBy = " ORDER BY c.AddTime DESC ";
            sql += strOrderBy;
            var data = Spa.RunSqLcmdDataSet(sql);
            if (data == null) return new DataTable();
            if (data.Tables.Count == 0) return new DataTable();
            return data.Tables[0];
        }

        public Boolean DeleteBorakerage(string billId)
        {
            string sql = string.Format(" delete from T_Borakerage where BillId ='{0}'", billId);
            return Spa.RunSqLcmd(sql);
        }
        #endregion

        #endregion
    }
}

